﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Configuration;
using TheBullysStore.Models.ModuloAdministracion.Convocatoria;
namespace TheBullysStore.Models.ModuloAdministracion.Reunion


{
    public class ReunionDAO
    {




        
        //public string modificarConvocatoria(ConvocatoriaBean convocatoriaBean)
        //{
        //    String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
        //    List<Tema> listaCargo = new List<Tema>();
        //    SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
        //    sqlCon.Open();
        //    try
        //    {
        //      SqlCommand sqlCmd ;
        //        string commandString="";
        //           commandString = "update convocatoria set Fecha='"+ convocatoriaBean.fecha+"' , Lugar ='"  + convocatoriaBean.lugar + "' where id_convocatoria= " + convocatoriaBean.id_convocatoria;
        //           sqlCmd = new SqlCommand(commandString, sqlCon);
        //          sqlCmd.ExecuteNonQuery();
        //          commandString = "delete convocatoria_x_tema  where id_convocatoria= " + convocatoriaBean.id_convocatoria;
        //          sqlCmd = new SqlCommand(commandString, sqlCon);
        //          sqlCmd.ExecuteNonQuery();  


        //        String[] aux;
        //          aux = convocatoriaBean.temas.Split('|');
        //          for (int i = 0; i < aux.Count(); i++)
        //          {
        //              if (!aux[i].Equals(""))
        //              {
        //                  commandString = "insert into convocatoria_x_tema values(" + convocatoriaBean.id_convocatoria + ",'" + aux[i] + "')";
        //                  sqlCmd = new SqlCommand(commandString, sqlCon);
        //                  sqlCmd.ExecuteNonQuery();
        //              }
        //          }
                







        //        sqlCon.Close();

               

        //        return "Se modifico la convocatoria correctamente";
        //    }
        //    catch (Exception ex)
        //    {
        //        sqlCon.Close();
        //        return "Se produjo un error al modificar la convocatoria";

        //    }


        //}




        public string insertarReunion(ReunionBean reu)
        {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
           
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {
                
                string commandString = "";
                SqlCommand sqlCmd;

                if (reu.accion == "1")
                {

                    commandString = "update convocatoria set estado=0 where id_convocatoria = " + reu.id_convocatoria;
                    sqlCmd = new SqlCommand(commandString, sqlCon);
                    sqlCmd.ExecuteNonQuery();
                    // commandString = "select max(id_convocatoria) from convocatoria";
                    //sqlCmd = new SqlCommand(commandString, sqlCon);
                    //if (!Convert.IsDBNull(sqlCmd.ExecuteScalar()))
                    //{
                    //    reu.id_convocatoria = (int)sqlCmd.ExecuteScalar();
                    //}
                    //else
                    //{

                    //    reu.id_convocatoria = 0;
                    //}
                    //reu.id_convocatoria = reu.id_convocatoria + 1;



                    commandString = "insert into reunion values('" + reu.fecha + "','" + reu.lugar + "','" + reu.conclusiones + "'," + reu.id_convocatoria + ",1," + reu.id_edificio + ",'" + reu.hora + "-" + reu.minuto + "')";
                    sqlCmd = new SqlCommand(commandString, sqlCon);
                    sqlCmd.ExecuteNonQuery();
                    commandString = "select max(id_reunion) from reunion";
                    sqlCmd = new SqlCommand(commandString, sqlCon);

                    reu.id_reunion = (int)sqlCmd.ExecuteScalar();




                    String[] aux;
                    aux = reu.temas.Split('|');
                    for (int i = 1; i < aux.Count(); i++)
                    {
                        if (!aux[i + 1].Equals(""))
                        {
                            commandString = "insert into reunion_x_tema (id_reunion,tema,observacion,conclusion) values(" + reu.id_reunion + ",'" + aux[i] + "',null,'" + aux[i + 1] + "')";

                            sqlCmd = new SqlCommand(commandString, sqlCon);
                            sqlCmd.ExecuteNonQuery();
                        }
                        i++;
                    }








                    sqlCon.Close();return "Se inserto la reunion correctamente";
                }
               
                    if (reu.accion == "2")
                    {
                        commandString = "update reunion set fecha_inicio='"+ reu.fecha +"' , fecha_fin='"+ reu.lugar +"' , conclusion_general='"+ reu.conclusiones +"' ,  hora='"+ reu.hora + "-" + reu.minuto + "' where id_reunion="+ reu.id_reunion +" ";
                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        sqlCmd.ExecuteNonQuery();


                        commandString = "delete reunion_x_tema where id_reunion=" + reu.id_reunion + " ";
                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        sqlCmd.ExecuteNonQuery();

                        String[] aux;
                        aux = reu.temas.Split('|');
                        for (int i = 1; i < aux.Count(); i++)
                        {
                            if (!aux[i + 1].Equals(""))
                            {
                                commandString = "insert into reunion_x_tema (id_reunion,tema,observacion,conclusion) values(" + reu.id_reunion + ",'" + aux[i] + "',null,'" + aux[i + 1] + "')";

                                sqlCmd = new SqlCommand(commandString, sqlCon);
                                sqlCmd.ExecuteNonQuery();
                            }
                            i++;
                        }








                        sqlCon.Close(); return "Se modifico la reunion correctamente";
                    }

                    if (reu.accion == "0")
                    {
                        commandString = "update convocatoria set estado=1 where id_convocatoria=" + reu.id_convocatoria;
                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        sqlCmd.ExecuteNonQuery();

                        commandString = "update reunion set estado=0 where id_convocatoria=" + reu.id_convocatoria;
                        sqlCmd = new SqlCommand(commandString, sqlCon);

                        sqlCmd.ExecuteNonQuery();









                        sqlCon.Close(); return "Se elimino la reunion correctamente";
                    }




                    return null;
                


                
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return "Se produjo un error al insertar la reunion";

            }


        }



        [HttpPost]
        public List<ReunionBean> buscarReunion(Filtro filtroo)
        {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
            List<ReunionBean> listaConvocatoria = new List<ReunionBean>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {

                string commandString = "select * from reunion where Estado=1 and id_edificio=" + filtroo.idEdificio + "  ";

                string filtro = "";

                //if (!(filtroo.lugar == null || filtroo.lugar.Trim().Equals("")))
                //{
                //    filtro = filtro + " and  lugar like '%" + filtroo.lugar.Trim() + "%'  ";
                //}


                if (!(filtroo.fechaInicial == null || filtroo.fechaInicial.Equals(Convert.ToDateTime("01/01/0001 12:00:00 AM"))))
                {
                    filtro = filtro + " and  Fecha_inicio >= '" + filtroo.fechaInicial + "'  ";
                }


                if (!(filtroo.fechaFinal == null || filtroo.fechaFinal.Equals(Convert.ToDateTime("01/01/0001 12:00:00 AM"))))
                {
                    filtro = filtro + " and  Fecha_inicio <= '" + filtroo.fechaFinal + "'  ";
                }

                commandString += filtro + " order by fecha_inicio desc";
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
                DateTime a;

                while (dataReader.Read())
                {
                    ReunionBean con = new ReunionBean();
                    //cliente. = (int)dataReader["idUsuario"];

                    con.hora = (string)dataReader["hora"];
                    con.lugar = (string)dataReader["fecha_fin"];
                    a = (DateTime)dataReader["fecha_inicio"];
                    con.fecha = a.ToShortDateString();
                    con.id_convocatoria = (int)dataReader["id_convocatoria"];
                    con.id_edificio = (int)dataReader["id_edificio"];
                    con.id_reunion = (int)dataReader["id_reunion"];
                    con.conclusiones = (string  )dataReader["conclusion_general"];
                    //   con.id_juntaPropietario = (int)dataReader["id_junta_propietario"];

                    listaConvocatoria.Add(con);
                }
                dataReader.Close();




                sqlCon.Close();

                return listaConvocatoria;
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return null;

            }


        }


        [HttpPost]
        public List<tabla> buscarTema2(int id_convocatoria)
        {
            String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
            List<tabla> listaTema = new List<tabla>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {

                string commandString = "select distinct tema,conclusion from reunion_x_tema where id_reunion= " + id_convocatoria;


                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
                tabla tb;
                while (dataReader.Read())
                {
                    tb = new tabla();
                    tb.tema = (string)dataReader["tema"];
                    tb.conclusion = (string)dataReader["conclusion"];


                    listaTema.Add(tb);
                }
                dataReader.Close();




                sqlCon.Close();

                return listaTema;
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return null;

            }


        }



        [HttpPost]
        public List< string> buscarTema(int id_convocatoria)
        {
             String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
            List<string> listaTema = new List<string>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {

                string commandString = "select distinct tema from convocatoria_x_tema where id_convocatoria= " + id_convocatoria;

               
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
                
                while (dataReader.Read())
                {
                    
                 
                    
                    listaTema.Add((string)dataReader["tema"]);
                }
                dataReader.Close();




                sqlCon.Close();

                return listaTema;
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return null;

            }


        }

        


    
    



        [HttpPost]
        public List< ConvocatoriaBean> buscarConvocatoria(Filtro filtroo)
        {
             String cadenaConfiguracion = ConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;
            List<ConvocatoriaBean> listaConvocatoria = new List<ConvocatoriaBean>();
            SqlConnection sqlCon = new SqlConnection(cadenaConfiguracion);
            sqlCon.Open();
            try
            {

                string commandString = "select * from Convocatoria where Estado=1  ";

                string filtro="";

             if (!(filtroo.lugar == null || filtroo.lugar.Trim().Equals("")))
                {
                    filtro = filtro + " and  lugar like '%" + filtroo.lugar.Trim() + "%'  ";
                }


             if (!(filtroo.fechaInicial == null || filtroo.fechaInicial.Equals(Convert.ToDateTime("01/01/0001 12:00:00 AM"))))
                {
                    filtro = filtro + " and  Fecha >= '" + filtroo.fechaInicial + "'  ";
                }


             if (!(filtroo.fechaFinal == null || filtroo.fechaFinal.Equals(Convert.ToDateTime("01/01/0001 12:00:00 AM"))))
                {
                    filtro = filtro + " and  Fecha <= '" + filtroo.fechaFinal + "'  ";
                }

                commandString+=filtro + " order by fecha desc";
                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                SqlDataReader dataReader = sqlCmd.ExecuteReader();
                DateTime a;
                while (dataReader.Read())
                {
                    ConvocatoriaBean con = new ConvocatoriaBean();
                    //cliente. = (int)dataReader["idUsuario"];


                    con.lugar = (string)dataReader["lugar"];
                    a = (DateTime)dataReader["fecha"];
                    con.fecha = a.ToShortDateString();
                    con.id_convocatoria = (int)dataReader["id_convocatoria"];
                    con.id_edificio = (int)dataReader["id_edificio"];
                    con.id_juntaPropietario = (int)dataReader["id_junta_propietario"];
                    
                    listaConvocatoria.Add(con);
                }
                dataReader.Close();




                sqlCon.Close();

                return listaConvocatoria;
            }
            catch (Exception ex)
            {
                sqlCon.Close();
                return null;

            }


        }

        


    
    }

}

      